import pymysql
import traceback


# -------- 1.连接MySQL -------------

def get_conn():
    conn = pymysql.connect(host="***",  # 本地ip或你的域名地址
                           user="***",  # MySQL登录用户名
                           password="***",  # MySQL登录密码
                           db="etc",  # 储存数据的数据库名
                           charset="utf8")
    # conn表示连接数据库，对数据的操作需要通过cursor来实现
    cursor = conn.cursor()
    return conn, cursor

def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()

# -------- 2.获取数据 -------------

def cksj():
    cursor = None
    conn = None
    try:
        # --------- 获取数据 ----------------
        conn, cursor = get_conn()
        sql = '''
                select count(XH),CKSJ
                from main
                group by CKSJ
                order by CKSJ
        '''
        cursor.execute(sql)
        data = cursor.fetchall()

        print(data)
        # 【output】（部分）
        # ((26, datetime.datetime(2020, 12, 22, 0, 0)), (32, datetime.datetime(2020, 12, 22, 0, 1)), (36, datetime.datetime(2020, 12, 22, 0, 2)),...)

        # --------- 加工数据 ---------------
        List = []  # 初始化数据集
        for i in data:
            # 将每秒的数据封装成echarts可以识别的种类
            item = {'name': i[1].strftime('%Y-%m-%d %H:%M:%S'),
                    'value': [i[1].strftime('%Y-%m-%d %H:%M:%S'), i[0]]}
            List.append(item)

        print(List)
        # 【output】（部分）
        # [{'name': '2020-12-22 00:00:00', 'value': ['2020-12-22 00:00:00', 26]}, {'name': '2020-12-22 00:01:00', 'value': ['2020-12-22 00:01:00', 32]},...]

        conn.commit()
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)

    return List


if __name__ == '__main__':
    cksj()